<?php
 if ( ! defined('BASEPATH')) exit('No direct script access allowed');
Class Muser extends MY_Model
{
    public function __construct()
    {
        parent::__construct();
        
    }
    
    public function getUser($number,$offset,$year,$month)
    {
        $limit = $offset ? intval($offset) : '0';
        $sql = "SELECT m.id AS mid,m.username,m.password,m.fullname,m.dateofbirth,m.email,m.type,m.job,m.`code`,m.joined,m.edit_last,m.fbid,m.`level`,m.active,DATE_FORMAT(m.created,'%H:%i:%s %d/%m/%Y') AS time,m.mobile,m.admin_id,`user`.id,`user`.username AS admin FROM member AS m LEFT JOIN `user` ON m.admin_id = `user`.id WHERE 1=1 ";
		
        $keyword = addslashes($this->input->post('keyword'));
        if(!in_array($keyword, array(null, '', '0')))
        {
            $sql .= "AND m.username like '%%".strtolower(trim($keyword))."%%' OR ";
            $sql .= " m.fullname like '%%".strtolower(trim($keyword))."%%' OR ";
            $sql .= " m.email like '%%".strtolower(trim($keyword))."%%' OR ";
            $sql .= " m.mobile like '%%".strtolower(trim($keyword))."%%' ";
        }
        if(!in_array($year, array(null, '', '0')))
        {
            $sql .= " AND (DATE_FORMAT(m.created,'%Y') = '".$year."') ";
        }
        if(!in_array($month, array(null, '', '0')))
        {
            $sql .= " AND (DATE_FORMAT(m.created,'%m') = '".$month."') ";
        }
		
        $sql_limit = " ORDER BY m.id DESC LIMIT ".$limit.",".$number."";
        $data = $this->db->query($sql.$sql_limit)->result_object();
        $count = $this->db->query($sql)->num_rows();
        
        $array = array();
        $array['count'] = $count;
        $array['data'] = $data;
        
        return $array;
    }
    
    public function getMemberid($id)
    {
        return $this->db->query('SELECT * FROM member WHERE id = '.$id)->row_object();
    }
    
    public function deleteAcc($id)
    {
        //die("DELETE FROM member WHERE id IN ('" . (is_array($id) ? implode("','", $id) : $id) . "')");
        $run = $this->db->query("DELETE FROM member WHERE id IN ('" . (is_array($id) ? implode("','", $id) : $id) . "')");
        if ($this->db->affected_rows() > 0)
        {
            return 200;  
        }
        return 201;
    }
    
    public function addMemberDb($admin_id)
    {
        $uname = $this->getParamString('email');
        $pword = md5(md5($uname).md5($this->getParamString('pword')));
        $arr = array(
                'username'         => $uname,
                'password'         => $pword,
                'fullname'         => $this->getParamString('fullname'),
                'email'            => $uname,
                'mobile'           => $this->getParamString('mobile'),
                'address'          => $this->getParamString('address'),
                'active'           => $this->getParamInt('active'),
                'admin_id'         => $admin_id,
                'created'          => date('Y-m-d H:i:s')
            );
        $this->db->insert('member', $arr);
        return 200;
    }
    
    public function editMemberDb($admin_id, $id)
    {
        $arr = array(
                'fullname'         => $this->getParamString('fullname'),
                'mobile'           => $this->getParamString('mobile'),
                'active'           => $this->getParamInt('active'),
                'address'          => $this->getParamString('address'),
                'admin_id'         => $admin_id
            );
        $pass_new = $this->getParamString('pword');
        if(!in_array($pass_new, array(null, '', '0')))
        {
            $username = $this->db->query('SELECT username FROM member WHERE id = '.$id)->row_object()->username;
            $arr['password'] = $pword = md5(md5($username).md5($pass_new));
        }
        $this->db->update('member', $arr, 'id = '.$id);
        return 200;
    }

    
    public function updateInfo($id)
    {
        $p = $this->getParamString('pword');
        $r_p = $this->getParamString('repword');
        if($p != $r_p)
        {
            return 203;
        }
        $data = array(
                    "fullname"      => $this->getParamString('fullname'),
                    "email"         => $this->getParamString('email'),
                    "mobile"        => $this->getParamString('mobile'),
                );
                
        if(!in_array($this->getParamString('pword'), array(null, '', '0')))
        {
            $salt           = $this->generateCode(5);
            $encode_pass    = md5($p);
            $encode_salt    = md5($encode_pass.$salt);
            $data['salt']   = $salt;
            $data['password']  = $encode_salt;
            
        }
        try{$this->db->update('user', $data, "id = '".$id."'"); return 200;} catch(exception $e) {}
    }
    
    public function getData($id)
    {
        $sql = "SELECT username,fullname,email,mobile,img,level,active,coin,subscriber,start_subscriber,end_subscriber,total_amount,codeConfirm FROM member WHERE id = '".$id."'";
        return $this->db->query($sql)->row_object();  
    }
    
    public function getUserExport()
    {
        return $this->db->query('SELECT m.username,m.fullname,m.dateofbirth,m.address,m.gender,m.email,m.mobile,DATE_FORMAT(m.created,"%H:%i:%s %d/%m/%Y") AS time, a.name AS age_name,j.name AS job_name, p.name AS province_name, d.name AS district_name 
		FROM member AS m LEFT JOIN age_groups AS a ON m.agegroup_id = a.id
			LEFT JOIN vn_job AS j ON j.id = m.job_id
			LEFT JOIN vn_province AS p ON p.provinceid = m.province_id
			LEFT JOIN vn_district AS d ON d.districtid = m.district_id WHERE 1=1
		ORDER BY m.id ASC')->result_object();
    }
	
	public function getUserExportCoupon()
    {
		/*$data = $this->db->query('SELECT content, action FROM setting WHERE `action` = "limitcoupon" ORDER BY id DESC')->result_object();
        $arr = array();
        foreach($data AS $el=>$le)
        {
            $exp = null;
            $exp = explode(',', $le->content);
            $form_coupon = $exp[0] == 1 ? 0 : $exp[0];
			$to_coupon = $exp[1];
        }*/
		// LIMIT '.$form_coupon.', '.$to_coupon
        return $this->db->query('SELECT m.username, m.fullname, m.email,eu.code_coupon,m.address, m.mobile, DATE_FORMAT(m.created,"%H:%i:%s %d/%m/%Y") AS time , a.name AS age_name,j.name AS job_name, p.name AS province_name, d.name AS district_name 
		FROM member AS m INNER JOIN ecoupon_user AS eu ON eu.user_create = m.id 
		LEFT JOIN age_groups AS a ON m.agegroup_id = a.id
			LEFT JOIN vn_job AS j ON j.id = m.job_id
			LEFT JOIN vn_province AS p ON p.provinceid = m.province_id
			LEFT JOIN vn_district AS d ON d.districtid = m.district_id WHERE 1=1
		ORDER BY m.id ASC')->result_object();
    }
	
	public function getUserExportCode()
    {
		/*$data = $this->db->query('SELECT content, action FROM setting WHERE `action` = "limitcode" ORDER BY id DESC')->result_object();
        $arr = array();
        foreach($data AS $el=>$le)
        {
            $exp = null;
            $exp = explode(',', $le->content);
            $form_code = $exp[0] == 1 ? 0 : $exp[0];
			$to_code = $exp[1];
        }
		// LIMIT '.$form_code.', '.$to_code
		*/
        return $this->db->query('SELECT m.username, m.fullname, m.email,cu.code_user,m.address, m.mobile, DATE_FORMAT(m.created,"%H:%i:%s %d/%m/%Y") AS time ,a.name AS age_name,j.name AS job_name, p.name AS province_name, d.name AS district_name, g.name AS gift_name , cu.user_create
		FROM member AS m INNER JOIN code_user AS cu ON cu.user_id = m.id 
			INNER JOIN vn_gift AS g ON g.id = cu.gift_id 
			LEFT JOIN age_groups AS a ON m.agegroup_id = a.id
			LEFT JOIN vn_job AS j ON j.id = m.job_id
			LEFT JOIN vn_province AS p ON p.provinceid = m.province_id
			LEFT JOIN vn_district AS d ON d.districtid = m.district_id WHERE 1=1
		ORDER BY m.id ASC')->result_object();
    }
	
	public function getProfile($id){
		return $this->db->query('SELECT id,username,fullname,mobile,email FROM `staff` WHERE id = '.$id.' LIMIT 1')->row_object();
	}
	
	public function updateProfileDb($username,$mobile,$fullname,$pword1,$pword2){
		if ( !in_array($pword1,array('', null)) && !in_array($pword2,array('', null)) && $pword1 == $pword2 ){
			$pw = md5(md5($username).md5($pword1));
			$data = array ('mobile' => $mobile, 'fullname' => $fullname, 'pword' => $pw );
		}else{
			$data = array ('mobile' => $mobile, 'fullname' => $fullname);
		}
		$this->db->update('staff', $data, 'id = '.$this->session->userdata('admin_id'));       
        $data__ = array(
                'fullname' => $fullname,
            );
        $this->session->set_userdata($data__);
        return 200;
	}
	
}